package com.ruge.excel;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ruge.excel.handler.ExcelHeader;
import com.ruge.excel.utils.Utils;

/**
 * 爱丽丝、如歌  创建于 2018/10/16 11:50
 * 说明:  Excel工具类
 */
public class ExcelUtil
{
	/**
	 *
	 * @Title: importExcelToListModel
	 * @Description:        excel导入
	 * @param path          Excel的路径
	 * @param clazz         待绑定的类(绑定属性注解)
	 * @param offsetLine    Excel表头行(默认是0)
	 * @param sheetIndex    Sheet索引(默认0)
	 * @return              返回转换为设置绑定的java对象集合
	 * @author: wu_jian
	 * @date:   2018年10月19日 下午3:56:35
	 */
	public static <T> List<T> importExcelToListModel ( String path, Class<T> clazz, int offsetLine, int sheetIndex )
	{
		int limitLine = Integer.MAX_VALUE;
		List<T> list = new ArrayList<T>();
		try
		{
			Workbook workbook = WorkbookFactory.create(new File(path));
			Sheet sheet = workbook.getSheetAt(sheetIndex);
			Row row = sheet.getRow(offsetLine);
			Map<Integer, ExcelHeader> maps = Utils.getHeaderMap(row, clazz);
			if (maps == null || maps.size() <= 0)
			{
				throw new RuntimeException("读取的Excel格式不正确，并检查是否设置了适当的行");
			}
			long maxLine = sheet.getLastRowNum() > ((long) offsetLine + limitLine) ? ((long) offsetLine + limitLine)
					: sheet.getLastRowNum();
			for (int i = offsetLine + 1; i <= maxLine; i++)
			{
				row = sheet.getRow(i);
				if (null == row)
				{
					continue;
				}
				T obj = null;
				try
				{
					obj = clazz.getDeclaredConstructor().newInstance();
				} catch (Exception e)
				{
					e.printStackTrace();
				}
				for (Cell cell : row)
				{
					int ci = cell.getColumnIndex();
					ExcelHeader header = maps.get(ci);
					if (null == header)
					{
						continue;
					}
					String val = (String) Utils.getCellValue(cell);
					Object value;
					String filed = header.getFiled();
					// 读取转换器
					if (null != header.getReadConverter())
					{
						value = header.getReadConverter().execRead(val);
					} else
					{
						// 默认转换
						value = Utils.getTargetClassByString(val, header.getFiledClazz());
					}
					Utils.copyProperty(obj, filed, value);
				}
				list.add(obj);
			}
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	/**
	 *
	 * @Title: exportListModelToExcel
	 * @Description: 基于注解的数据导出
	 * @param data  待导出数据
	 * @param clazz 映射对象Class
	 * @param fileName
	 * @param isWriteHeader 是否写入表头
	 * @param sheetName     指定导出Excel的sheet名称
	 * @param isXSSF        导出的Excel是否为Excel2007及以上版本(默认是)
	 * @param response      生成的Excel输出全路径   response.getOutputStream()
	 * @author: wu_jian
	 * @date:   2018年10月19日 下午3:55:39
	 */
	@SuppressWarnings({ "resource", "rawtypes" })
	public static void exportListModelToExcel ( List<?> data, Class clazz, String fileName,boolean isWriteHeader, String sheetName,
												boolean isXSSF, HttpServletResponse response )
	{
		try
		{
			response.reset();
			response.setHeader("Content-disposition",
					"attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1")); // 解决火狐中文乱码
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setCharacterEncoding("GBK");
			response.setHeader("Pragma", "no-cache");
			response.setHeader("Cache-Control", "no-cache");
			response.setDateHeader("Expires", 0);
			Workbook workbook;
			if (isXSSF)
			{
				workbook = new XSSFWorkbook();
			} else
			{
				workbook = new HSSFWorkbook();
			}
			Sheet sheet;
			if (null != sheetName && !"".equals(sheetName))
			{
				sheet = workbook.createSheet(sheetName);
			} else
			{
				sheet = workbook.createSheet();
			}
			Row row = sheet.createRow(0);
			List<ExcelHeader> headers = Utils.getHeaderList(clazz);
			if (isWriteHeader)
			{
				// 写标题
				for (int i = 0; i < headers.size(); i++)
				{
					row.createCell(i).setCellValue(headers.get(i).getTitle());
				}
			}
			// 写数据
			Object _data;
			for (int i = 0; i < data.size(); i++)
			{
				row = sheet.createRow(i + 1);
				_data = data.get(i);
				for (int j = 0; j < headers.size(); j++)
				{
					row.createCell(j).setCellValue(
							Utils.getProperty(_data, headers.get(j).getFiled(), headers.get(j).getWriteConverter()));
				}
			}
			workbook.write(response.getOutputStream());
		} catch (Exception e)
		{
			e.printStackTrace();
		}

	}
}
